import os,datetime

from utils.DBService import sqlquery

CHAOBIAO_SQL = """SELECT
        b.departname,
        SUM(czpanshu) AS czpanshu,
        CONVERT(DECIMAL(10, 2), SUM(CONVERT(FLOAT, b.czpanshu))/SUM(CONVERT(FLOAT, b.zpanshu)) * 100) as zchuzhiL,
        SUM(cjiczpanshu) AS cjiczpanshu,
        CONVERT(DECIMAL(10, 2), SUM(CONVERT(FLOAT, b.cjiczpanshu))/SUM(CONVERT(FLOAT, b.cjpanshu)) * 100) as cjchuzhiL,
        SUM(zjiczpanshu) AS zjiczpanshu,
        CONVERT(DECIMAL(10, 2), SUM(CONVERT(FLOAT, b.zjiczpanshu))/SUM(CONVERT(FLOAT, b.zjpanshu)) * 100) as zjchuzhiL,
        SUM(gjiczpanshu) AS gjiczpanshu,
        CONVERT(DECIMAL(10, 2), SUM(CONVERT(FLOAT, b.gjiczpanshu))/SUM(CONVERT(FLOAT, b.gjpanshu)) * 100) as gjchuzhiL
FROM
    (
        SELECT
            SUM(a.panshu) AS panshu
        FROM
            TONGJICLHNTVIew a
        JOIN
            t_s_depart AS c ON a.departid = c.id
        WHERE
            a.orgcode LIKE (
                SELECT
                    org_code
                FROM
                    t_s_depart
                WHERE
                    id = '{}'
            ) + '%'
            AND (c.org_type = 9 or c.org_type = 3)
            and c.depart_order not in (3,4)
            {} {}
    ) AS a,
    (
        SELECT
            b.departname,
            SUM(a.cbpanshu) AS zpanshu,
            SUM(a.ccbpanshu) AS cjpanshu,
            SUM(a.mcbpanshu) AS zjpanshu,
            SUM(a.hcbpanshu) AS gjpanshu,
            SUM(a.czpanshu) AS czpanshu,
            SUM(a.cczpanshu) AS cjiczpanshu,
            SUM(a.mczpanshu) AS zjiczpanshu,
            SUM(a.hczpanshu) AS gjiczpanshu
        FROM
            TONGJICBHNTView AS a
        JOIN
            t_s_depart AS b ON a.departid = b.id
        WHERE
            a.orgcode LIKE (
                SELECT
                    org_code + '%'
                FROM
                    t_s_depart
                WHERE
                    id = '{}'
            )
            AND (b.org_type = 9 or b.org_type = 3)
            and b.depart_order not in (3,4)
            {}{}
        GROUP BY
            b.departname
    ) AS b
GROUP BY b.departname;"""

def chuzhi_ovStatic(Args,user):
    print('Quest参数:\n\t',Args)
    depId = Args['depId']
    dates = Args['dates']
    materials = Args['materials']
    depId = '\',\''.join(depId.split(','))

    if depId == '8a8ab0b246dc81120146dc8180ba0017' or \
            Args['depId'] == 'All':
        depId = '8a8ab0b246dc81120146dc8180ba0017'
    elif depId == 'f6323b406b4fab1b016bacabdb9f01d3':
        depId = 'f6323b406b4fab1b016bacabdb9f01d3'
    elif depId == 'f6323b406b4fab1b016bacac674901d5':
        depId = 'f6323b406b4fab1b016bacac674901d5'
    elif depId == 'f6323b406d19fc87016d1a5c43910016':
        depId = 'f6323b406d19fc87016d1a5c43910016'
    elif depId == 'f6323b406d19fc87016d1a5b861b0014':
        depId = 'f6323b406d19fc87016d1a5b861b0014'
    else:
        depId = '8a8ab0b246dc81120146dc8180ba0017'

    dateWhere = 'and CONVERT(nvarchar(10), a.chuliaodate, 120)in (\'{}\')'
    datearray = dates.split(',')
    if len(datearray) == 2:
        dateWhere = 'and CONVERT(nvarchar(10), a.chuliaodate, 120)>=\'{}\' and CONVERT(nvarchar(10), a.chuliaodate, 120)<=\'{}\''.format(
            datearray[0], datearray[1])
    else:
        dateWhere = dateWhere.format('\',\''.join(dates.split(',')))
    materials = '\',\''.join(materials.split(','))
    if Args['materials'] != 'All':
        clWhere = 'AND a.jiaozuobuwei like \'%{}%\' '.format(materials)
    else: clWhere = ''
    print(materials)
    banhezhanSql = CHAOBIAO_SQL.format(depId,dateWhere,clWhere,depId,dateWhere,clWhere,)
    banhezhanData = sqlquery(CHAOBIAO_SQL.format(depId,dateWhere,clWhere,depId,dateWhere,clWhere))
    print('SQL:\n\t', banhezhanSql)

    slData = []
    resdata = []
    if banhezhanData.__len__() == 0:
        marktitle = '当前时间没有数据'
        return resdata,slData,marktitle
    else:
        if len(datearray) == 2:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            end = datetime.datetime.strptime(datearray[1], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '到' + str(
                end.year) + '年' + str(end.month) + '月' + str(end.day) + '日' + '处置情况如下:'
        else:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '处置情况如下:'

    for banhezhan in banhezhanData:
        if os.environ.get("ENV_NAME")=='1':
            banhezanmincheng,zpanshu,zchaobL, \
            cjpanshu,cjichaobL,zjipanshu,zjichaobL,gjpanshu,gjichaobL \
                = banhezhan[0],banhezhan[1],banhezhan[2],banhezhan[3], \
                  banhezhan[4],banhezhan[5],banhezhan[6],banhezhan[7],banhezhan[8]
        else:
            banhezanmincheng,zpanshu,zchaobL, \
            cjpanshu,cjichaobL,zjipanshu,zjichaobL,gjpanshu,gjichaobL, \
                =banhezhan[0],banhezhan[1],banhezhan[2],banhezhan[3], \
                 banhezhan[4],banhezhan[5],banhezhan[6],banhezhan[7],banhezhan[8]
        slData.append([
            banhezanmincheng, \
            cjpanshu,cjichaobL,zjipanshu,zjichaobL,gjpanshu,gjichaobL
        ])
        resdata.append([
            banhezanmincheng, \
            cjpanshu,cjichaobL,zjipanshu,zjichaobL,gjpanshu,gjichaobL
        ])
    print('Data:\n\t', slData)

    a_list = []
    b_list = []
    children = []
    for item in slData:
        station_name = item[0]
        a = item[0:]
        data = {
            'key': slData.index(item),
            'departname': a[0],
            'cjpanshu': a[1],
            'cjichaobL': float(a[2]),
            'zjpanshu': a[3],
            'zjichaobL': float(a[4]),
            'gjpanshu': a[5],
            'gjichaobL': float(a[6])
        }
        table_head = [
            {
                "title": '站点名称',
                "dataIndex": list(data.keys())[1],
                "key": list(data.keys())[1],
            },
            {
                "title": '初级处置盘数',
                "dataIndex": list(data.keys())[2],
                "key": list(data.keys())[2],
            },
            {
                "title": '初级处置率(%)',
                "dataIndex": list(data.keys())[3],
                "key": list(data.keys())[3],
            },
            {
                "title": '中级处置盘数',
                "dataIndex": list(data.keys())[4],
                "key": list(data.keys())[4],
            },
            {
                "title": '中级处置率(%)',
                "dataIndex": list(data.keys())[5],
                "key": list(data.keys())[5],
            },
            {
                "title": '高级处置盘数',
                "dataIndex": list(data.keys())[6],
                "key": list(data.keys())[6],
            },
            {
                "title": '高级处置率(%)',
                "dataIndex": list(data.keys())[7],
                "key": list(data.keys())[7],
            }]
        if station_name.startswith("武黄1标"):
            a_list.append(data)
        else:
            b_list.append(data)
    if len(a_list)>0:
        add_totals(a_list)
        childrenA_Data = {"children":[{"title": '武黄1标拌合站', "data": {"tableHead": table_head, "tableBody": a_list}}] ,"title": '武黄1标'}
        children.append(childrenA_Data)
    if len(b_list)>0:
        add_totals(b_list)
        childrenB_Data = {"children":[{"title": '武黄2标拌合站', "data": {"tableHead": table_head, "tableBody": b_list}}] ,"title": '武黄2标'}
        children.append(childrenB_Data)

    result = [{"title": "武黄高速", "children": children}]

    print("result:\n\t",result)
    return resdata,result,marktitle;

def add_totals(data):
    # 计算合计值
    total_cjichaobL = sum(item["cjichaobL"] for item in data)
    total_zjpanshu = sum(item["zjpanshu"] for item in data)
    total_cjpanshu = sum(item["cjpanshu"] for item in data)
    total_zjichaobL = sum(item["zjichaobL"] for item in data)
    total_gjpanshu = sum(item["gjpanshu"] for item in data)
    total_gjichaobL = sum(item["gjichaobL"] for item in data)

    # 添加合计项
    total_item = {
        "cjichaobL": total_cjichaobL,
        "cjpanshu": total_cjpanshu,
        "departname": "合计",
        "key": "",
        "zjpanshu": total_zjpanshu,
        "zjichaobL":total_zjichaobL,
        "gjpanshu":total_gjpanshu,
        "gjichaobL":total_gjichaobL
    }

    data.append(total_item)

    return data


functionConf = {
    "name": "chuzhi_ovStatic",
    "description": "key point: 超标处置,Query out-of-standard processing data by ID, time, and name.  "
                   "eg: Query exceeding conditions for concrete production, where the number of produced batches exceeds the specified total batches, based on ID, time, and name.",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department IDs. All就是查询武黄高速(eg:8a8ab0b246dc81120146dc8180ba0017)"
            },
            "dates": {
                "type": "string",
                "description": "Date(s) of material production in yyyy-MM-dd format. Use comma-separated values (e.g., '2023-01-01,2023-10-31')."
            },
            "materials": {
                "type": "string",
                "description": "jiaozuobuwei.Use comma-separated values (e.g., '新民岭中桥,桥头岭大桥').除了项目/标段/站点   Use 'All' for all items."
            }
        },
        "required": [
            "depId",
            "dates",
            'materials'
        ]
    }
}
